<?php

require_once(PATH_TO_APP . "/components/excel/Classes/PHPExcel.php");
require_once(PATH_TO_APP . "/components/tabledatagateway/ExcelExportGateway.php");

class ExcelExport
{
    private $arr_values_;
    private $output_handler_;
    private $arr_cols_;
    private $table_details_;
    private $table_gateway_;
    private $where_condition_;
    
    const EXCEL_START_ROW = 5;
    
    const EXCEL_DATA_START_CELL = 2;

    public function __construct($arr_values, $table_details, $where_condition, $singleton_registry)
    {
        $this->arr_values_ = $arr_values;
        $this->output_handler_ = $singleton_registry->getSingleton('OutputHandler');
        $this->table_gateway_ = new ExcelExportGateway();
        $this->arr_cols_ = range('A', 'Z');
        $this->table_details_ = $table_details;
        $this->where_condition_ = $where_condition;
    }
        
    public function __destruct(){}
    
    public function export()
    {
        try{
            $obj_php_excel = new PHPExcel();
            $cell_start = constant("self::EXCEL_START_ROW");
            $has_dimensions = true;
            foreach($this->arr_values_['meta'] as $key => $value)
            {            
                $obj_php_excel->setActiveSheetIndex(0)->setCellValue('A5', 'Region_ID');
                $obj_php_excel->setActiveSheetIndex(0)->setCellValue('B5', 'Region');
                if(isset($value['dimension']) && $value['dimension'] > 1)
                {
                    $m_cell = $cell_start+$value['dimension']-1;
                    $obj_php_excel->getActiveSheet()->mergeCells('A5:A'.$m_cell);
                }
                $cell = constant("self::EXCEL_DATA_START_CELL");
                for($cnt=1; $cnt <= $value['iteration']; ++$cnt)
                {
                    if(count($value['values']) > 0)
                    {
                        foreach($value['values'] as $ikey => $ivalue)
                        {
                            $cell_letter = $this->calculateCellLetter($cell);
                            $obj_php_excel->setActiveSheetIndex(0)->setCellValue($cell_letter . $cell_start, $ivalue);
                            if(isset($value['length']) && $value['length'] > 1)
                            {
                                $end_cell = $this->calculateCellLetter(($cell+$value['length']-1));
                                $obj_php_excel->getActiveSheet()->mergeCells($this->arr_cols_[$cell] . $cell_start .':'.$end_cell . $cell_start);
                                $cell += $value['length']-1;
                            }
                            ++$cell;
                        }
                    }
                    else
                    {
                        $has_dimensions = false;
                        $cell_letter = $this->calculateCellLetter($cell);
                        $obj_php_excel->setActiveSheetIndex(0)->setCellValue($cell_letter . $cell_start, 'Cases');
                        ++$cell;
                    }
                }
                ++$cell_start;
            }
            $cells = 0;
            foreach($this->arr_values_['values'] as $key => $arr_item)
            {
                $obj_php_excel->setActiveSheetIndex(0)->setCellValue('B' . $cell_start, $key);
                $cell = constant("self::EXCEL_DATA_START_CELL");
                foreach($arr_item as $item)
                {
                    $obj_php_excel->setActiveSheetIndex(0)->setCellValue('A' . $cell_start, $item['region_id']);
                    $cell_letter = $this->calculateCellLetter($cell);
                    ++$cell;
                    $obj_php_excel->getActiveSheet()->getStyle($cell_letter . $cell_start)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED1);
                    $obj_php_excel->setActiveSheetIndex(0)->setCellValue($cell_letter . $cell_start, str_replace(",", ".", str_replace(".", "", $item['cases'])));
                }
                ++$cell_start;
                $cells = $cell;
            }
            for($cnt=0; $cnt<$cells; ++$cnt)
                $obj_php_excel->getActiveSheet()->getColumnDimension($this->calculateCellLetter($cnt))->setAutoSize(true);
            $obj_php_excel->setActiveSheetIndex(0)->setCellValue('A1', $this->table_details_->dt_titel);
            $obj_php_excel->getActiveSheet()->setTitle(preg_replace("/[^0-9A-Za-z]/", '', $this->table_details_->dt_titel));
            if($has_dimensions)
                $this->writeSecondSheet($obj_php_excel);
            $obj_php_excel->setActiveSheetIndex(0);
            $this->output_handler_->addHeader('Content-Type', 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            $this->output_handler_->addHeader('Content-Disposition', 'attachment; filename="'.preg_replace("/[^0-9A-Za-z]/", '', $this->table_details_->dt_titel).'.xlsx"');
            $this->output_handler_->showHeaders();
            $obj_writer = PHPExcel_IOFactory::createWriter($obj_php_excel, 'Excel2007');
            $obj_writer->save('php://output');
            return true;
        }
        catch(Exception $e)
        {
            echo $e->getMessage();
            return false;
        }
    }
    
    private function writeSecondSheet($obj_php_excel)
    {
        $obj_php_excel->createSheet();
        $obj_php_excel->setActiveSheetIndex(1);
        $arr_values = $this->table_gateway_->getExcelData($this->table_details_->dt_tabelle, $this->table_details_->e_id);
        if(count($arr_values) == 0)
            return false;
        $cell_start=1;
        $cell = 0;
        foreach($arr_values[0] as $key => $item)
        {
            if($key != 'gid')
            {
                $cell_letter = $this->calculateCellLetter($cell);
                ++$cell;
                $obj_php_excel->setActiveSheetIndex(1)->setCellValue($cell_letter . $cell_start, ucfirst($key));
            }
        }
        ++$cell_start;
        foreach($arr_values as $arr_item)
        {
            $cell = 0;
            foreach($arr_item as $key => $item)
            {
                if($key != 'gid')
                {
                    $cell_letter = $this->calculateCellLetter($cell);
                    ++$cell;
                    $obj_php_excel->setActiveSheetIndex(1)->setCellValue($cell_letter . $cell_start, $item);
                }
            }
            ++$cell_start;
        }
        for($cnt=0; $cnt<$cell_start; ++$cnt)
            $obj_php_excel->getActiveSheet()->getColumnDimension($this->calculateCellLetter($cnt))->setAutoSize(true);
    }
    
    private function calculateCellLetter($cell)
    {
        $mod = $cell % count($this->arr_cols_);
        $cell_letter = $this->arr_cols_[$mod];
        $pos = (int)($cell / count($this->arr_cols_));
        if($pos > 0)
        {
            if($pos < count($this->arr_cols_))
                $cell_letter = $this->arr_cols_[$pos-1] . $cell_letter;
        }
        return $cell_letter;
    }
}
 
?>
